package com.edu.dao.impl;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.rowset.CachedRowSet;

import com.edu.dao.IUserDao;
import com.edu.util.DBUtil;
import com.edu.util.PageData;
import com.edu.entity.Admins;
import com.edu.entity.Users;


public class UserDaoImpl implements IUserDao{

	@Override
	/**
	 *陆亮  查询已注册且状态启用的用户数量
	 *@return 总人数
	 */
	public int queryUsersCount() {
		String sql="select count(*) from users where userStatus=1";
		CachedRowSet crs = DBUtil.execQuery(sql);
		int usercount=0;
		try {
			while (crs.next()) {
				int sum=crs.getInt(1);	
				usercount=sum;
			}
		} catch (SQLException e) {	
			e.printStackTrace();
		}
		return usercount;
	}
	
	/**
	 * 用户登录
	 * @param userAccount 账户
	 * @param userPwd 密码
	 * @return 用户信息
	 */
	@Override
	public Users userLogin(String userAccount, String userPwd) {
		// TODO Auto-generated method stub
		Users users=null;
		CachedRowSet crs=DBUtil.execQuery("select * from users where userAccount=? and userPwd=?", userAccount,userPwd);
		try {
			while (crs.next()) {
				int userid=crs.getInt(1);
				String useraccount=crs.getString(2);
				String userpwd=crs.getString(3);
				String userimg=crs.getString(4);
				int userscore=crs.getInt(5);
				int userstatus=crs.getInt(6);
				String userdate=crs.getString(7);
				users=new Users(userid, useraccount, userpwd, userimg, userscore, userstatus,userdate);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return users;
	}

	/**
	 * 用户注册
	 * @param users 用户信息
	 * @return turn 添加成功  false  添加失败
	 */
	@Override
	public boolean userRegister(Users users) {
		// TODO Auto-generated method stub
		if (users!=null) {
			String sql1="insert into users values(null,?,MD5(?),?,0,1,now())";
			String sql2="insert into userinfos values(null,null,?,?,?)";
			DBUtil.executeUpdate(sql1,users.getUserAccount(),users.getUserPwd(),users.getUserImg());
			DBUtil.executeUpdate(sql2, users.getUserName(),users.getUserPhone(),users.getUserMail());
			return true;
		}
		return false;
	}

	/**
	 * 用户名查重
	 * @param userAccount 用户名
	 * @return turn 用户名存在  false 用户名不存在
	 */
	@Override
	public boolean checkName(String userAccount) {
		// TODO Auto-generated method stub
		Users users=null;
		CachedRowSet crs=DBUtil.execQuery("select * from users where userAccount=?",userAccount);
		try {
			while (crs.next()) {
				int userid=crs.getInt(1);
				String userName=crs.getString(2);
				String userpwd=crs.getString(3);
				String userimg=crs.getString(4);
				int userscore=crs.getInt(5);
				int userstatus=crs.getInt(6);
				String userdate=crs.getString(7);
				users=new Users(userid, userName, userpwd, userimg, userscore, userstatus, userdate);
			}
			if (users!=null) {
				return true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}
    
    /**
     * 查询用户两个表格的全字段
     * @author 陆亮
     * @return 包含users的list
     */
	public List<Users> queryAllUsers() {
		String sql="select * from users u inner join userinfos f on u.userId=f.userId";
		CachedRowSet crs=DBUtil.execQuery(sql);
		Users users=null;
		List<Users> list = new ArrayList<Users>();
		try {
			while (crs.next()) {
				int userid=crs.getInt(1);
				String userAccount=crs.getString(2);
				String userpwd=crs.getString(3);
				String userimg=crs.getString(4);
				int userScore=crs.getInt(5);
				int userstatus=crs.getInt(6);
				String userDate=crs.getString(7);
				String userName=crs.getString(10);
				long userPhone=crs.getLong(11);
				String userMail=crs.getString(12);
				users = new Users(userid,userAccount,userpwd,userimg,userScore,userstatus,userName,userPhone,userMail,userDate);
			    list.add(users);
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	
	/**
	 * @author gyx
	 */
	@Override
	public List<Users> queryAllUser() {
		Users users = null;
		List<Users> list = new ArrayList<Users>();
		CachedRowSet crs = DBUtil.execQuery("select u.userId,u.userAccount,u.userPwd,u.userImg,u.userScore,u.userStatus,i.adminId,i.userName,i.userPhone,i.userMail,u.userDate from users as u,userinfos as i where u.userId = i.userId");
		try {
			while (crs.next()) {
				 int userId= crs.getInt(1);
			     String  userAccount = crs.getString(2);
				 String  userPwd = crs.getString(3);
				 String  userImg = crs.getString(4);
				 int userScore = crs.getInt(5);
				 int userStatus = crs.getInt(6);
				 String adminAccount = crs.getString(7);
				 String  userName = crs.getString(8);
				 long userPhone = crs.getLong(9);
				 String userMail = crs.getString(10);
				 String userDate = crs.getString(11);
				 Admins admin= new Admins(adminAccount);
				 
				users = new Users(userId, userAccount, userPwd, userImg, userScore, userStatus, admin, userName, userPhone, userMail, userDate);
				list.add(users);				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public List<Users> queryUserByAccount(String userAccount) {
		Users users = null;
		List<Users> list = new ArrayList<Users>();
		CachedRowSet crs = DBUtil.execQuery("select u.userId,u.userAccount,u.userPwd,u.userImg,u.userScore,u.userStatus,i.adminId,i.userName,i.userPhone,i.userMail,u.userDate from users as u left join userinfos as i on u.userId = i.userId where userAccount like ?","%"+userAccount+"%");
		try {
			while (crs.next()) {
				 int userId= crs.getInt(1);
			     String  userAccount1 = crs.getString(2);
				 String  userPwd = crs.getString(3);
				 String  userImg = crs.getString(4);
				 int userScore = crs.getInt(5);
				 int userStatus = crs.getInt(6);
				 String adminAccount = crs.getString(7);
				 String  userName = crs.getString(8);
				 long userPhone = crs.getLong(9);
				 String userMail = crs.getString(10);
				 String userDate = crs.getString(11);
				 Admins admin= new Admins(adminAccount);
				 
				users = new Users(userId, userAccount1, userPwd, userImg, userScore, userStatus, admin, userName, userPhone, userMail, userDate);
				list.add(users);				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public int countNewUsers() {
		String sql="select count(*) from users where to_days(userDate)=to_days(now())";
		CachedRowSet crs = DBUtil.execQuery(sql);
		int newUserCount=0;
		try {
			while (crs.next()) {
				int sum=crs.getInt(1);	
				newUserCount=sum;
			}
		} catch (SQLException e) {	
			e.printStackTrace();
		}
		return newUserCount;
	}
    
	@Override
	public int countYesterdayUsers() {
		String sql="select count(*) from users where to_days(now()) - to_days(userDate) <=1";
		CachedRowSet crs = DBUtil.execQuery(sql);
		int yesterdayUserCount=0;
		try {
			while (crs.next()) {
				int sum=crs.getInt(1);	
				yesterdayUserCount=sum;
			}
		} catch (SQLException e) {	
			e.printStackTrace();
		}
		return yesterdayUserCount;
	}
	
	
	@Override
	public List<Users> queryNewUser() {
		Users users = null;
		List<Users> list = new ArrayList<Users>();
		CachedRowSet crs = DBUtil.execQuery("select u.userId,u.userAccount,u.userPwd,u.userImg,u.userScore,u.userStatus,i.adminId,i.userName,i.userPhone,i.userMail,u.userDate from users as u, userinfos as i where u.userId = i.userId order by userDate desc limit 0,3");
		try {
			while (crs.next()) {
				 int userId= crs.getInt(1);
			     String  userAccount = crs.getString(2);
				 String  userPwd = crs.getString(3);
				 String  userImg = crs.getString(4);
				 int userScore = crs.getInt(5);
				 int userStatus = crs.getInt(6);
				 String adminAccount = crs.getString(7);
				 String  userName = crs.getString(8);
				 long userPhone = crs.getLong(9);
				 String userMail = crs.getString(10);
				 String userDate = crs.getString(11);
				 Admins admin= new Admins(adminAccount);
				 
				users = new Users(userId, userAccount, userPwd, userImg, userScore, userStatus, admin, userName, userPhone, userMail, userDate);
			    list.add(users);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;		
	}

	@Override
	public boolean logoutUser(int userId) {
		//0禁用  1启用
		return DBUtil.executeUpdate("update users set userStatus=0 where userId=?",userId);
	}

	@Override
	public boolean startUser(int userId) {
		return DBUtil.executeUpdate("update users set userStatus=1 where userId=?",userId);
	}

	@Override
	//默认加一分
	public boolean addUserScore(int userId) {
		// TODO Auto-generated method stub
		 return DBUtil.executeUpdate("UPDATE  users SET userScore=userScore+1 WHERE userId=?",userId);
	}
    
	/**
	 * @author 陆亮  查询当日注册用户数
	 */
	@Override
	public int queryNewUserCount() {
		// TODO Auto-generated method stub
		String sql="select count(userId) from users where date(userDate) = curdate() and userStatus=1";
		CachedRowSet crs=DBUtil.execQuery(sql);
		int newUser=0;
		try {
			while (crs.next()) {
				newUser=crs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return newUser;
	}

	/**
	 * @author gyx  分页查询
	 */
	@Override
	public PageData queryUserByLikePage(int pageNo,int pageSize,String accountkey) {
		//获取分页的数据
		String sql= "select u.userId,u.userAccount,u.userPwd,u.userImg,u.userScore,u.userStatus,i.adminId,i.userName,i.userPhone,i.userMail,u.userDate from users as u left join userinfos as i on u.userId = i.userId where userAccount like ? order by userStatus desc limit ?,? ";
		CachedRowSet crs = DBUtil.execQuery(sql, "%"+accountkey+"%",(pageNo-1)*pageSize,pageSize);
		Users users = null;
		List<Users> list = new ArrayList<Users>();
		try {
			while (crs.next()) {
				 int userId= crs.getInt(1);
			     String  userAccount = crs.getString(2);
				 String  userPwd = crs.getString(3);
				 String  userImg = crs.getString(4);
				 int userScore = crs.getInt(5);
				 int userStatus = crs.getInt(6);
				 String adminAccount = crs.getString(7);
				 String  userName = crs.getString(8);
				 long userPhone = crs.getLong(9);
				 String userMail = crs.getString(10);
				 String userDate = crs.getString(11);
				 Admins admin= new Admins(adminAccount);
				 
				users = new Users(userId, userAccount, userPwd, userImg, userScore, userStatus, admin, userName, userPhone, userMail, userDate);
			    list.add(users);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		//获取分页的总条数
		int totalCount = 0;
		String sql2 = "select count(*) from users where userAccount like ?";
		CachedRowSet crs2 = DBUtil.execQuery(sql2,"%"+accountkey+"%");
		try {
			while (crs2.next()) {
				totalCount = crs2.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		PageData pageData=new PageData(list,pageNo,pageSize,totalCount);
		return pageData;		
	}
    
	/**
	 * @author 陆亮
	 * 查询近7日新注册用户
	 */
	@Override
	public int[] query7DayUsers() {
		//存储数据的数组
				int [] sum=new int[7];
				//数组下标索引
				int n=0;
				for (int i =sum.length; i >0; i--) {
					String sql="SELECT count(*) FROM users where  to_days(now()) - to_days(userDate) = ?";
					CachedRowSet crs=DBUtil.execQuery(sql, i);
					try {
						while(crs.next()) {
							 sum[n]=crs.getInt(1);
							 n++;
						}
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				}
				return sum;
	}


}
